# %%
# 确认数据文件
import numpy as np
import pandas as pd

import reader
from common import *

(year, month), _ = year_month()

FP_CHAIJI = f"report/1-拆机/{year}{month:02}_拆机工单地市稽核明细表.xlsx"
FP_KUANDAI = (
    f"report/2-宽带装移机/{year}{month:02}_宽带装移机工单地市稽核明细表.xlsx"
)
FP_TV = f"report/3-电视装移机/{year}{month:02}_电视装移机地市稽核明细表.xlsx"
FP_IMS = (
    f"report/4-IMS装移机/{year}{month:02}_IMS装移机工单地市稽核明细表.xlsx"
)
FP_ZUWANG = (
    f"report/5-智能组网/{year}{month:02}_智能组网工单地市稽核明细.xlsx"
)
FP_TISU = f"report/6-提速/{year}{month:02}_提速工单稽核明细表.xlsx"
FP_ANFANG = (
    f"report/7-智能安防/{year}{month:02}_安防工单地市稽核结果.xlsx"
)
FP_AJTV = (
    f"report/8-爱家TV/{year}{month:02}_爱家TV开通地市稽核明细.xlsx"
)

check_files(
    FP_CHAIJI,
    FP_KUANDAI,
    FP_TV,
    FP_IMS,
    FP_ZUWANG,
    FP_TISU,
    FP_ANFANG,
    FP_AJTV,
)

# %%
# 读取数据
df_chaiji = pd.read_excel(FP_CHAIJI)
df_kuandai = pd.read_excel(FP_KUANDAI)
df_tv = pd.read_excel(FP_TV)
df_ims = pd.read_excel(FP_IMS)
df_zuwang = pd.read_excel(FP_ZUWANG)
df_tisu = pd.read_excel(FP_TISU)
df_anfang = pd.read_excel(FP_ANFANG)

df_ajtv = pd.read_excel(FP_AJTV)
df_ajtv.rename(
    columns={
        "订单行号": "工单号",
        "CRM订单号": "CRM业务流水号",
    },
    inplace=True,
)
df_ajtv.工单号 = df_ajtv.工单号.astype("string")
df_ajtv.电视账号 = df_ajtv.电视账号.astype("string")

# %%
# 筛选【地市稽核结果】通过的工单
df_list = [
    df_chaiji,
    df_kuandai,
    df_tv,
    df_ims,
    df_zuwang,
    df_tisu,
    df_anfang,
    df_ajtv,
]
for df in df_list:
    df.drop(df[df.地市稽核结果 != S_PASSED].index, inplace=True)

df_anfang_jcb = df_anfang[df_anfang.业务结算类型 == "普通版"]
df_anfang_paxc = df_anfang[df_anfang.业务结算类型 == "平安乡村"]
df_zuwang_sbaz = df_zuwang[df_zuwang.服务类型 == "设备安装"]
df_zuwang_cfw = df_zuwang[df_zuwang.服务类型 == "纯服务"]

df_chaiji["是否拆机"] = S_YES
df_kuandai["是否宽带开通"] = S_YES
df_tv["是否电视开通"] = S_YES
df_ims["是否IMS开通"] = S_YES
df_tisu["是否提速"] = S_YES
df_anfang_jcb["是否安防（基础版）"] = S_YES
df_anfang_paxc["是否安防（平安乡村）"] = S_YES
df_zuwang_sbaz["是否智能组网（设备安装）"] = S_YES
df_zuwang_cfw["是否智能组网（纯服务）"] = S_YES
df_ajtv["是否爱家TV"] = S_YES

df_yw = pd.concat(
    [
        df_chaiji,
        df_kuandai,
        df_tv,
        df_ims,
        df_tisu,
        df_anfang_jcb,
        df_anfang_paxc,
        df_zuwang_sbaz,
        df_zuwang_cfw,
        df_ajtv,
    ],
    ignore_index=True,
)


# %%
# 定义字段
COLUMNS_DY = [
    "月份",
    "地市",
    "工单号",
    "CRM业务流水号",
    "派单人",
    "派单时间",
    "归档时间",
    "时长",
    "操作类型",
    "装维人员/用户班人员",
    "五级地址ID",
    "客户类型",
    "接入方式",
    "宽带帐号",
    "电视账号",
    "多媒体家庭电话",
]

COLUMNS_YW = [
    "是否宽带开通",
    "是否电视开通",
    "是否IMS开通",
    "是否爱家TV",
    "是否智能组网（设备安装）",
    "是否智能组网（纯服务）",
    "是否安防（平安乡村）",
    "是否安防（基础版）",
    "是否提速",
    "是否拆机",
]


# %%
# 读取当月归档工单
FP_GONGDAN_DANGYUE = (
    f"data/0-工单/家客工单导出_潮州_{year}-{month:02}.zip"
)
df_dy = reader.read_gongdan(FP_GONGDAN_DANGYUE)
df_dy = pd.concat([df_dy, df_ajtv], ignore_index=True)
df_dy["时长"] = (
    df_dy["归档时间"] - df_dy["派单时间"]
) / np.timedelta64(1, "h")
df_dy["月份"] = f"{month}月"
df_dy["地市"] = "潮州"

# %%
# 拼接数据
df_yw[COLUMNS_YW] = df_yw[COLUMNS_YW].fillna(S_NO)
tmp = pd.merge(
    df_yw[["工单号"] + COLUMNS_YW],
    df_dy[COLUMNS_DY],
    on="工单号",
    how="left",
)

df = tmp[COLUMNS_DY + COLUMNS_YW]

# %%
# 同装汇总表
df.to_excel(
    f"report/0-汇总/{year}{month:02}_同装汇总表.xlsx",
    index=False,
)

# %%
# 完成
